<?php
class Search_Model_Selareasearch extends Apm_Model_Base{

    /**
     * 駅一覧データ検索
     *
     * @access    public
     * @param     array
     * @return    Array 駅一覧データ
     */
    public function getEnsenEkinameList($params)
    {
        $db_params = array();
        $sql = $this->generateEnsenEkinameSql($params, $db_params);
        return $this->dbGetList($sql, $db_params);
    }

    /**
     * 町一覧データ検索
     *
     * @access    public
     * @param     array
     * @return    Array 駅一覧データ
     */
    public function getSikucyosonnameList($params)
    {
        $db_params = array();
        $sql = $this->generateSikucyosonSql($params, $db_params);
        return $this->dbGetList($sql, $db_params);
    }

    /**
     * 市区町村の町名データを取得
     * @param unknown $params
     */
    public function getChouList($params){

        $db_params = array();

        $sql = "
            SELECT CHOU.T55_TODOUFUKEN_CD AS TODOUFUKEN_CD, CHOU.T55_SHIKUCHOUSON_CD AS SHIKUCHOUSON_CD,
             		SHI.T57_SHIKUCHOUSON AS SHIKUCHOUSON, CHOU.T55_CHOUMEI_CD AS CHOUMEI_CD,
            		CHOU.T55_CHOUMEI AS CHOUMEI,T55_CHOUMEI_CNT AS CHOUMEI_CNT
             FROM  T55CHOUMEI_SUM CHOU INNER JOIN T57SEARCH_SHIKUCHOUSON_SUM SHI
             		ON CHOU.T55_TODOUFUKEN_CD = SHI.T57_TODOUFUKEN_CD AND CHOU.T55_SHIKUCHOUSON_CD = SHI.T57_SHIKUCHOUSON_CD
            		AND CHOU.T55_DATA_KUBUN = SHI.T57_DATA_KUBUN
             WHERE  CHOU.T55_TODOUFUKEN_CD = '".$params['prefCd']."' AND  T55_SHIKUCHOUSON_CD IN (".$params['cityCds'].")  AND CHOU.T55_DATA_KUBUN = 0 AND CHOU.T55_CHOUMEI_CNT > 0
             ORDER BY SHI.T57_SHIKUCHOUSON_CD, CHOU.T55_CHOUMEI_CD
            ";
        return $this->dbGetList($sql, $db_params);
    }

	/**
	* 市区町村一覧データ取得
	*/
	public function getShikucyosonData($params, $bknSearchFlg)
    {
        $db_params = array();
        $sql = $this->generateShikucyosonSql($params, $db_params, $bknSearchFlg);
        return $this->dbGetList($sql, $db_params);
    }

	/**
	* 都道府県一覧データ取得
	*/
	public function getTodohukenList()
    {
        $db_params = array();
        $sql = $this->generateTodohukenListSql();
        $data = $this->dbGetList($sql, $db_params);
        $list = array();
        foreach($data as $key => $val){
        	$list[$val['TODOUFUKEN_CD']] = $val['TODOUFUKEN'];
        }
        return $list;
    }

	/**
	* エリア一覧データ取得
	*   キーを都道府県コード、値にエリア名形式で返す
	*/
	public function getAreaListByPrefCdKey()
    {
        $db_params = array();
        $sql = $this->generateAreaSql();
        $area_data = $this->dbGetList($sql, $db_params);

        $area_list = array();
        foreach($area_data as $val){

        	$area_list[$val['M01_TODOUFUKEN_CD']] = $val['M01_AREA_ROMAN_NAME'];
        }

        return $area_list;
    }

	/**
	* エリア一覧データ取得 物件詳細kodawariData処理変更
	*   キーを都道府県コード、値にエリアコード形式で返す
	*/
	public function getAreaCdListByPrefCdKey()
    {
        $db_params = array();
        $sql = $this->generateAreaSql();
        $area_data = $this->dbGetList($sql, $db_params);

        $area_list = array();
        foreach($area_data as $val){

        	$area_list[$val['M01_TODOUFUKEN_CD']] = $val['M01_AREA_CD'];
        }

        return $area_list;
    }

	/**
	* エリアコードからエリア一覧データ取得
	*/
	public function getAreaListByAreaCd($params)
    {
        $db_params = array();
        $sql = $this->generateAreaListByAreaCdSql($params, $db_params);
        return $this->dbGetList($sql, $db_params);
    }

	/**
	* エリアコードからエリア名取得
	*/
	public function getAreaNameByAreaCd($params)
    {
        $db_params = array();
        $sql = $this->generateAreaNameByAreaCdSql($params, $db_params);
        return $this->dbGetData($sql, $db_params);
    }


	/**
	* 都道府県コードからエリアデータ取得
	*/
	public function getAreaListByPrefCd($params)
    {
        $db_params = array();
        $sql = $this->generateAreaListByPrefCdSql($params, $db_params);
        return $this->dbGetList($sql, $db_params);
    }

	/**
	* 都道府県コードから都道府県名取得
	*/
	public function getPrefNameByPrefCd($params)
    {
        $db_params = array();
        $sql = $this->generatePrefNameByPrefCdSql($params, $db_params);
        return $this->dbGetList($sql, $db_params);
    }

	/**
	* エリア一覧データ取得
	*   キーを都道府県コード、値にエリア名形式で返す
	*/
	public function getSimpleArea($params)
    {
    	$db_params = array();
        $sql = $this->generateSimpleAreaSql($params, $db_params);
        return $this->dbGetData($sql, $db_params);
    }

	/**
	* エリアURLからエリアデータ取得
	*/
	public function getRomanArea($params)
    {
    	$db_params = array();
        $sql = $this->generateRomanAreaSql($params, $db_params);
        return $this->dbGetData($sql, $db_params);
    }

	/**
	 *  都道府県コードから市区町村（5件）データ取得
	 */
	public function getPopCityListByPrefCd($params)
	{
		$db_params = array();
		$sql = $this->generatePopCityListByPrefCd($params, $db_params);
		return $this->dbGetList($sql, $db_params);
	}

   /**
     * 市区町村データ検索用SQL文の取得
     *
     * 市区町村一覧データ検索用SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @param	string $bknSearchFlg 1:物件の存在する市区町村のみ検索
     * @return	string 市区町村データ検索用SQL
     */
    public function generateShikucyosonSql($params, $db_params, $bknSearchFlg)
    {
		$sql = "";
		$sql .= "SELECT ";
		$sql .= "   M43.M43_TODOUFUKEN AS TODOUFUKEN,";
		$sql .= "   T1.T57_ENSEN_SHIKUCHOUSON_CNT AS REC_CNT,";
		$sql .= "   M43.M43_TODOUFUKEN_CD AS TODOUFUKEN_CD,";
		$sql .= "   TRIM(M43.M43_TODOUFUKEN_CD || T1.T57_ENSEN_SHIKUCHOUSON_CD) AS SHIKUCHOUSON_CD,";
		$sql .= "   T1.T57_ENSEN_SHIKUCHOUSON_NAME AS SHIKUCHOUSON,";
		$sql .= "   NVL(T2.PREF_HEYA_CNT,0) AS PREF_HEYA_CNT ";
		$sql .= "FROM ";
		$sql .= "   (SELECT ";
		$sql .= "   T57_TODOUFUKEN_CD, ";
		$sql .= "   T57_ENSEN_SHIKUCHOUSON_CD, ";
		$sql .= "   T57_ENSEN_SHIKUCHOUSON_NAME,";
		$sql .= "   T57_ENSEN_SHIKUCHOUSON_CNT";
		$sql .= "   FROM ";
		$sql .= "   T57ENSEN_SHIKUCHOUSON_SUM ";
		$sql .= "WHERE ";
		$sql .= "       T81SEARCH_PARAM = ";
		$sql .= "   AND T57_DATA_KUBUN = 0 ";
		$sql .= "   ) T1, ";
		$sql .= "   (SELECT ";
		$sql .= "   SUM(T57_ENSEN_SHIKUCHOUSON_CNT) AS PREF_HEYA_CNT, ";
		$sql .= "   T57_TODOUFUKEN_CD ";
		$sql .= "   FROM ";
		$sql .= "   T57ENSEN_SHIKUCHOUSON_SUM ";
		$sql .= "   WHERE ";
		$sql .= $this->createWhere("T57_TODOUFUKEN_CD",$params['PrefCd']);
		$sql .= "   AND T57_ENSEN_SHIKUCHOUSON_FLAG = 1 ";
		$sql .= "   AND T57_DATA_KUBUN = 0 ";
		$sql .= "   GROUP BY ";
		$sql .= "   T57_TODOUFUKEN_CD ";
		$sql .= " ) T2,";
		$sql .= "( SELECT M43_TODOUFUKEN_CD, M43_TODOUFUKEN FROM M43TODOUFUKEN ";
		$sql .= "        WHERE ";
		$sql .= $this->createWhere("M43_TODOUFUKEN_CD",$params['PrefCd']);
		$sql .= "  ) M43 ";
		$sql .= "WHERE ";
		$sql .= "   M43.M43_TODOUFUKEN_CD = T1.T57_TODOUFUKEN_CD(+) ";
		$sql .= "   AND M43.M43_TODOUFUKEN_CD = T2.T57_TODOUFUKEN_CD(+) ";
    	if ($bknSearchFlg == '1')
		{
			$sql .= "AND NVL(T1.T57_ENSEN_SHIKUCHOUSON_CNT, 0) > 0 ";
		}
		$sql .= "ORDER BY ";
		$sql .= "   M43_TODOUFUKEN_CD ASC, SHIKUCHOUSON_CD ASC";

        return $sql;
    }

    /**
     * 駅一覧データ検索用SQL文の取得
     *
     * @access    private
     * @param     array
     * @param     array ※参照渡し
     * @return    string 駅データ検索用SQL
     */
    private function generateEnsenEkinameSql($params, &$db_params)
    {
    	if($params['StWord'])  $db_params['stWord']  = $this->convParam($params);
        if($params['stEnsen']) $db_params['stEnsen'] = $params['StEnsen'];
        $sql = "";
            $sql .= "SELECT";
            $sql .= "    T2.TODOUFUKEN_CD AS TODOUFUKEN_CD";
            $sql .= "    ,M43.M43_TODOUFUKEN AS TODOUFUKEN";
            $sql .= "    ,T2.RENRAKU_EKI_CD AS RENRAKU_EKI_CD";
            $sql .= "    ,T2.EKI_CD AS EKI_CD";
            $sql .= "    ,T2.EKI_NAME AS EKI_NAME";
            $sql .= "    ,NVL(T1.EKI_CNT,0) AS REC_CNT";
            $sql .= "    ,T2.ENSEN_CD AS ENSEN_CD";
            $sql .= "    ,T2.ENSEN_NAME AS ENSEN_NAME";
            $sql .= "    ,T2.IDO AS IDO";
            $sql .= "    ,T2.KEIDO AS KEIDO";
            $sql .= "  FROM";
            $sql .= "    (";
            $sql .= "        SELECT";
            $sql .= "            M03_TODOUFUKEN_CD as TODOUFUKEN_CD";
            $sql .= "            ,M03_TODOUFUKEN as TODOUFUKEN";
            $sql .= "            ,M03_ENSEN_CD as ENSEN_CD";
            $sql .= "            ,M03_ENSEN_NAME as ENSEN_NAME";
            $sql .= "            ,M03_EKI_CD as EKI_CD";
            $sql .= "            ,M03_EKI_NAME as EKI_NAME";
            $sql .= "            ,NVL(M03_RENRAKU_EKI_CD,M03_EKI_CD) AS RENRAKU_EKI_CD";
            $sql .= "            ,M03_IDO as IDO";
            $sql .= "            ,M03_KEIDO as KEIDO";
            $sql .= "          FROM";
            $sql .= "            M03EKI";
            $sql .= "         WHERE";
            $sql .= "            1=1";
            if($db_params['stWord']){
                $sql .= "            AND (M03_EKI_NAME LIKE '%' || :stWord || '%'";
                $sql .= "            OR M03_EKI_NAME LIKE '%' || :stWord || '駅%')";
            }
            if($db_params['stEnsen']){
                $sql .= "            AND M03_ENSEN_CD = :stEnsen";
            }

        if ('11' == $params['SrhKbn'])
        {
            // 地図検索
            $sql .= "            AND M03_IDO IS NOT NULL";
            $sql .= "            AND M03_KEIDO IS NOT NULL";
        }
            $sql .= "    ) T2";
            $sql .= "    ,(";
            $sql .= "        SELECT";
            $sql .= "            T54_EKI_CNT as EKI_CNT";
            $sql .= "            ,T54_EKI_CD as EKI_CD";
            $sql .= "          FROM";
            $sql .= "            T54EKI_SUM";
            $sql .= "         WHERE";
            $sql .= "            T54_DATA_KUBUN = 0";
            $sql .= "    ) T1";
            $sql .= "    ,M43TODOUFUKEN M43";
            $sql .= " WHERE";
            $sql .= "    T2.TODOUFUKEN_CD = M43.M43_TODOUFUKEN_CD";
            $sql .= "    AND T2.EKI_CD = T1.EKI_CD(+)";
            $sql .= " GROUP BY";
            $sql .= "    T2.TODOUFUKEN_CD";
            $sql .= "    ,M43.M43_TODOUFUKEN";
            $sql .= "    ,T2.RENRAKU_EKI_CD";
            $sql .= "    ,T2.EKI_CD";
            $sql .= "    ,T2.EKI_NAME";
            $sql .= "    ,T1.EKI_CNT";
            $sql .= "    ,T2.ENSEN_CD";
            $sql .= "    ,T2.ENSEN_NAME";
            $sql .= "    ,T2.IDO";
            $sql .= "    ,T2.KEIDO";
            $sql .= " ORDER BY";
            $sql .= "    TODOUFUKEN_CD ASC";
            $sql .= "    ,RENRAKU_EKI_CD ASC";
            $sql .= "    ,EKI_NAME ASC";

            return $sql;
    }

    /**
     * 駅一覧データ検索用SQL文の取得
     *
     * @access    private
     * @param     array
     * @param     array ※参照渡し
     * @return    string 駅データ検索用SQL
     */
    private function generateSikucyosonSql($params, &$db_params)
    {
        if($params['SelCity']) $db_params['selCity'] = $params['SelCity'];

        $sql = "";
		$sql .= "SELECT";
		$sql .= "        T1.T55_CHOUMEI_CNT AS REC_CNT";
		$sql .= "        ,T1.T55_TODOUFUKEN_CD || T1.T55_SHIKUCHOUSON_CD AS SHIKUCHOUSON_CD";
		$sql .= "        ,T1.M01_TODOUFUKEN || T1.M01_SHIKUCHOUSON AS SHIKUCHOUSON";
		$sql .= "        ,T1.T55_TODOUFUKEN_CD || T1.T55_SHIKUCHOUSON_CD || T1.T55_CHOUMEI_CD AS CHOUMEI_CD";
		$sql .= "        ,T1.T55_CHOUMEI AS CHOUMEI";
		$sql .= "        ,T1.M01_CHOUMEI_KANA AS KANA";
		$sql .= "        ,T2.SHIKUCHOUSON_HEYA_CNT";
		$sql .= "    FROM";
		$sql .= "        (";
		$sql .= "            SELECT";
		$sql .= "                    T55_TODOUFUKEN_CD";
		$sql .= "                    ,T55_SHIKUCHOUSON_CD";
		$sql .= "                    ,T55_CHOUMEI_CD";
		$sql .= "                    ,M01_CHOUMEI_KANA";
		$sql .= "                    ,M01_TODOUFUKEN";
		$sql .= "                    ,M01_SHIKUCHOUSON";
		$sql .= "                    ,T55_CHOUMEI";
		$sql .= "                    ,T55_CHOUMEI_CNT";
		$sql .= "                FROM";
		$sql .= "                    T55CHOUMEI_SUM";
		$sql .= "                    ,M01JYUUSHO";
		$sql .= "                WHERE";
		$sql .= "                        T55_SHIKUCHOUSON_CD = '". $db_params['selCity'] ."'";
		$sql .= "                    AND T55_DATA_KUBUN = 0";
		$sql .= "                    AND T55_TODOUFUKEN_CD = M01_TODOUFUKEN_CD";
		$sql .= "                    AND T55_SHIKUCHOUSON_CD = M01_SHIKUCHOUSON_cd";
		$sql .= "                    AND T55_CHOUMEI_CD = M01_CHOUMEI_CD";
		$sql .= "        ) T1";
		$sql .= "        ,(";
		$sql .= "            SELECT";
		$sql .= "                    T57_ENSEN_SHIKUCHOUSON_CNT AS SHIKUCHOUSON_HEYA_CNT";
		$sql .= "                    ,T57_TODOUFUKEN_CD";
		$sql .= "                    ,T57_ENSEN_SHIKUCHOUSON_CD";
		$sql .= "                FROM";
		$sql .= "                    T57ENSEN_SHIKUCHOUSON_SUM";
		$sql .= "                WHERE";
		$sql .= "                        T57_ENSEN_SHIKUCHOUSON_CD = '" . $db_params['selCity'] ."'";
		$sql .= "                    AND T57_ENSEN_SHIKUCHOUSON_FLAG = 1";
		$sql .= "                    AND T57_DATA_KUBUN = 0";
		$sql .= "        ) T2";
		$sql .= "    WHERE";
		$sql .= "        T1.T55_TODOUFUKEN_CD = T2.T57_TODOUFUKEN_CD";
		$sql .= "        AND T1.T55_SHIKUCHOUSON_CD = T2.T57_ENSEN_SHIKUCHOUSON_CD";
		$sql .= "    GROUP BY";
		$sql .= "        T1.T55_CHOUMEI_CNT";
		$sql .= "        ,T1.T55_TODOUFUKEN_CD";
		$sql .= "        ,T1.T55_SHIKUCHOUSON_CD";
		$sql .= "        ,T1.T55_CHOUMEI_CD";
		$sql .= "        ,T1.T55_CHOUMEI";
		$sql .= "        ,T1.M01_CHOUMEI_KANA";
		$sql .= "        ,T1.M01_TODOUFUKEN";
		$sql .= "        ,T1.M01_SHIKUCHOUSON";
		$sql .= "        ,T2.SHIKUCHOUSON_HEYA_CNT";
		$sql .= "    ORDER BY";
		$sql .= "        SHIKUCHOUSON_CD ASC";
		$sql .= "        ,KANA ASC";

            return $sql;
    }

   /**
     * エリア一覧取得用SQL文の取得
     *
     * 市区町村一覧データ検索用SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @param	string $bknSearchFlg 1:物件の存在する市区町村のみ検索
     * @return	string 市区町村データ検索用SQL
     */
    public function generateAreaSql()
    {
		$sql = "";
		$sql .= "SELECT ";
		$sql .= "    M01_TODOUFUKEN_CD ";
		$sql .= "   ,M01_AREA_CD ";
		$sql .= "   ,M01_AREA_NAME ";
		$sql .= "   ,M01_AREA_ROMAN_NAME ";
		$sql .= "FROM ";
		$sql .= "    M01AREA ";

        return $sql;
    }

   /**
     * エリア一覧取得用SQL文の取得
     *
     * 市区町村一覧データ検索用SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @return	string 市区町村データ検索用SQL
     */
    public function generateSimpleAreaSql($param, &$db_params)
    {

    	$db_params['prefCd'] = $param;

    	$sql = "";
		$sql .= "SELECT ";
		$sql .= "    AREA.M01_TODOUFUKEN_CD ";
		$sql .= "   ,AREA.M01_AREA_CD ";
		$sql .= "   ,AREA.M01_AREA_NAME ";
		$sql .= "   ,AREA.M01_AREA_ROMAN_NAME ";
		$sql .= "   ,SE.M43_SETSUMEI_HEADLINE ";
		$sql .= "   ,SE.M43_SETSUMEI_DTL ";
		$sql .= "   ,SE.M43_SEO_LINK_TITLE_SOUBA ";
		$sql .= "   ,SE.M43_SEO_LINK_TITLE_GOTOUCHI ";
		$sql .= "   ,SE.M43_SEO_LINK_TITLE_TOKUCHO ";
		$sql .= "   ,SE.M43_SEO_LINK_URL_SOUBA ";
		$sql .= "   ,SE.M43_SEO_LINK_URL_GOTOUCHI ";
		$sql .= "   ,SE.M43_SEO_LINK_URL_TOKUCHO ";
		$sql .= "FROM ";
		$sql .= "    M01AREA AREA LEFT JOIN M43TODOUFUKEN_SETSUMEI SE ON AREA.M01_TODOUFUKEN_CD = SE.M43_TODOUFUKEN_CD ";
		$sql .= "WHERE ";
		$sql .= "    M01_TODOUFUKEN_CD = :prefCd ";

        return $sql;
    }

   /**
     * エリア取得用SQL文の取得
     *
     * エリアデータ検索用SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @return	string エリアデータ検索用SQL
     */
    public function generateRomanAreaSql($param, &$db_params)
    {

    	$db_params['areaCd'] = $param;

    	$sql = "";
		$sql .= "SELECT ";
//		$sql .= "    distinct M01_AREA_CD ";
		$sql .= "    M01_AREA_CD ";
		$sql .= "   ,M01_AREA_NAME ";
		$sql .= "   ,M01_AREA_ROMAN_NAME ";
		$sql .= "FROM ";
		$sql .= "    M01AREA ";
		$sql .= "WHERE ";
		$sql .= "     M01_AREA_CD = :areaCd ";
		$sql .= " AND ROWNUM = 1 ";

        return $sql;
    }

   /**
     * エリア一覧取得用SQL文の取得
     *
     * 市区町村一覧データ検索用SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @return	string 市区町村データ検索用SQL
     */
    public function generateAreaListByAreaCdSql($params, &$db_params)
    {

    	if(is_array($params['AreaCd'])){
    		$db_params['areaCd'] = $params['AreaCd'][0];
    	}else{
    		$db_params['areaCd'] = $params['AreaCd'];
    	}

    	$sql = "";
		$sql .= "SELECT ";
//		$sql .= "    distinct M2.M01_TODOUFUKEN_CD ";
//		$sql .= "   ,M2.M01_TODOUFUKEN ";
		$sql .= "    M2.M43_TODOUFUKEN_CD AS M01_TODOUFUKEN_CD ";
		$sql .= "   ,M2.M43_TODOUFUKEN AS M01_TODOUFUKEN ";
		$sql .= "   ,M01_AREA_CD ";
		$sql .= "   ,M01_AREA_NAME ";
		$sql .= "FROM ";
//		$sql .= "    M01AREA M1 , M01JYUUSHO M2 ";
		$sql .= "    M01AREA M1 , M43TODOUFUKEN M2 ";
		$sql .= "WHERE ";
//		$sql .= "        M1.M01_TODOUFUKEN_CD = M2.M01_TODOUFUKEN_CD(+) ";
		$sql .= "        M1.M01_TODOUFUKEN_CD = M2.M43_TODOUFUKEN_CD ";
		$sql .= "    AND M1.M01_AREA_CD = :areaCd ";
		$sql .= "ORDER BY ";
//		$sql .= "        M2.M01_TODOUFUKEN_CD ";
		$sql .= "        M2.M43_TODOUFUKEN_CD ";

        return $sql;
    }

   /**
     * エリア一覧取得用SQL文の取得
     *
     * 市区町村一覧データ検索用SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @return	string 市区町村データ検索用SQL
     */
    public function generateAreaNameByAreaCdSql($params, &$db_params)
    {

    	$db_params['areaCd'] = $params['AreaCd'];

    	$sql = "";
		$sql .= "SELECT ";
//		$sql .= "    distinct M01_AREA_NAME ";
		$sql .= "    M01_AREA_NAME ";
		$sql .= "FROM ";
		$sql .= "    M01AREA ";
		$sql .= "WHERE ";
		$sql .= "     M01_AREA_CD = :areaCd ";
		$sql .= " AND ROWNUM = 1 ";

        return $sql;
    }

   /**
     * エリア一覧取得用SQL文の取得
     *
     * 市区町村一覧データ検索用SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @return	string 市区町村データ検索用SQL
     */
    public function generateAreaListByPrefCdSql($params, &$db_params)
    {

    	$db_params['prefCd'] = $params['PrefCd'][0];

    	$sql = "";
		$sql .= "SELECT ";
//		$sql .= "    distinct M2.M01_TODOUFUKEN_CD ";
//		$sql .= "   ,M2.M01_TODOUFUKEN ";
		$sql .= "    M2.M43_TODOUFUKEN_CD AS M01_TODOUFUKEN_CD ";
		$sql .= "   ,M2.M43_TODOUFUKEN AS M01_TODOUFUKEN ";
		$sql .= "   ,M01_AREA_CD ";
		$sql .= "   ,M01_AREA_NAME ";
		$sql .= "FROM ";
//		$sql .= "    M01AREA M1 , M01JYUUSHO M2 ";
		$sql .= "    M01AREA M1 , M43TODOUFUKEN M2 ";
		$sql .= "WHERE ";
//		$sql .= "        M1.M01_TODOUFUKEN_CD = M2.M01_TODOUFUKEN_CD(+) ";
		$sql .= "        M1.M01_TODOUFUKEN_CD = M2.M43_TODOUFUKEN_CD ";
		$sql .= "    AND M1.M01_AREA_CD = (SELECT M01_AREA_CD FROM M01AREA WHERE M01_TODOUFUKEN_CD = :prefCd ) ";
		$sql .= "ORDER BY ";
//		$sql .= "        M2.M01_TODOUFUKEN_CD ";
		$sql .= "        M2.M43_TODOUFUKEN_CD ";

        return $sql;
    }

   /**
     * 都道府県名取得用SQL文の取得
     *
     * 都道府県名用SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @return	array 都道府県名取得用SQL
     */
    public function generatePrefNameByPrefCdSql($params, &$db_params)
    {

    	foreach($params['PrefCd'] as $key => $val){
    		$db_params['prefCd' . $key] = $val;
    	}

    	$sql = "";
		$sql .= "SELECT ";
//		$sql .= "    distinct M01_TODOUFUKEN_CD ";
//		$sql .= "    ,M01_TODOUFUKEN ";
		$sql .= "     M43_TODOUFUKEN_CD AS M01_TODOUFUKEN_CD ";
		$sql .= "    ,M43_TODOUFUKEN AS M01_TODOUFUKEN ";
		$sql .= "    ,M43_TODOUFUKEN_ALIAS AS M01_TODOUFUKEN_ALIAS ";
		// 2014.10.31 add t_kumagaink
		$sql .= "    ,M01.M01_AREA_ROMAN_NAME ";
		// end add
		$sql .= "FROM ";
//		$sql .= "    M01JYUUSHO ";
		$sql .= "    M43TODOUFUKEN M43 ";
		// 2014.10.31 add t_kumagaink
		$sql .= "LEFT JOIN ";
		$sql .= "    M01AREA M01 ";
		$sql .= "ON M01.M01_TODOUFUKEN_CD = M43.M43_TODOUFUKEN_CD ";
		// end add
		$sql .= "    ";
		$sql .= "WHERE ";
//		$sql .= "        M01_TODOUFUKEN_CD IN ( ";
		$sql .= "        M43_TODOUFUKEN_CD IN ( ";
		$sepa = "";
		foreach($params['PrefCd'] as $key => $val){
			$sql .= $sepa . " :prefCd" . $key;
			$sepa = ",";
		}
		$sql .= "        ) ";
		$sql .= "ORDER BY ";
//		$sql .= "        M01_TODOUFUKEN_CD ";
		$sql .= "        M43_TODOUFUKEN_CD ";

        return $sql;
    }

	/**
	* 都道府県コードから市区町村を取得するSQL
	*
	* @access	public
	* @param	array
	* @param	array ※参照渡し
	* @return	string sql
	*/
	public function generatePopCityListByPrefCd($params, &$db_params)
	{
		//$db_params['strTypeCd'] = $params['TatemonoTypeCd'];
		//$db_params['strKodawariId'] = $params['KodawariId'];

		$sqlWhere  = "";
		$sqlWhere .= " AND (";
		$sepa = "";
		foreach ($params['PrefCd'] as $key => $val) {
			$sqlWhere .= $sepa." M97.M97_TODOUFUKEN_CD = :strPrefCd_".$key;
			$db_params['strPrefCd_'.$key] = $val;
			$sepa = " OR ";
		}
		$sqlWhere .= ") ";

		$sql  = "";
		$sql .= "SELECT DISTINCT";
		$sql .= "     M01.M01_TODOUFUKEN_CD TODOUFUKEN_CD";
		$sql .= "    ,M01.M01_TODOUFUKEN TODOUFUKEN";
		$sql .= "    ,M01.M01_TODOUFUKEN_CD || M01.M01_SHIKUCHOUSON_CD SHIKUCHOUSON_CD";
		$sql .= "    ,M01.M01_SHIKUCHOUSON SHIKUCHOUSON_NAME";
		$sql .= "    ,'999' SITEITOSI_CD";
		$sql .= "    ,null SITEITOSI_ROMAN";
		$sql .= "    ,M97.M97_ORDER_NO";
		$sql .= " FROM";
		$sql .= "     M97POPULAR_PAGE M97";
		$sql .= "    ,M01JYUUSHO M01";
		$sql .= " WHERE";
		$sql .= "    M97.M97_PAGE_KBN = '5'";
		$sql .= "    AND M97.M97_PAGE_CD = M01.M01_TODOUFUKEN_CD || M01.M01_SHIKUCHOUSON_CD";
		$sql .= $sqlWhere;
		//$sql .= "    AND M97.M97_TATEMONO_TYPE_CD = :strTypeCd ";
		//$sql .= "    AND M97.M97_KODAWARI_CD = :strKodawariId ";
		$sql .= "    AND M97.M97_ORDER_NO <= 5";

		$sql .= " UNION ALL ";

		$sql .= "SELECT DISTINCT";
		$sql .= "     M44.M44_TODOUFUKEN_CD TODOUFUKEN_CD";
		$sql .= "    ,null TODOUFUKEN";
		$sql .= "    ,null SHIKUCHOUSON_CD";
		$sql .= "    ,M44.M44_SITEITOSI SHIKUCHOUSON_NAME";
		$sql .= "    ,M44.M44_SITEITOSI_CD SITEITOSI_CD";
		$sql .= "    ,M44.M44_SITEITOSI_ROMAN SITEITOSI_ROMAN";
		$sql .= "    ,M97.M97_ORDER_NO";
		$sql .= " FROM";
		$sql .= "     M97POPULAR_PAGE M97";
		$sql .= "    ,M44SITEITOSI M44";
		$sql .= " WHERE";
		$sql .= "    M97.M97_PAGE_KBN = '5'";
		$sql .= "    AND M97.M97_PAGE_CD = M44.M44_SITEITOSI_CD";
		$sql .= $sqlWhere;
		//$sql .= "    AND M97.M97_TATEMONO_TYPE_CD = :strTypeCd ";
		//$sql .= "    AND M97.M97_KODAWARI_CD = :strKodawariId ";
		$sql .= "    AND M97.M97_ORDER_NO <= 5";

		$sql .= " ORDER BY";
		$sql .= "     TODOUFUKEN_CD";
		$sql .= "    ,SITEITOSI_CD";
		$sql .= "    ,SHIKUCHOUSON_CD";

		return $sql;
	}

	/**
	* エリア都道府県一覧データを表示用に変換
	*
	* @access	public
	* @param	array ※参照渡し
	* @return	void
	*/
	public function convertAreaPrefData(&$data, $params)
	{
		$data['Mapping'] = array();
		$prefArr = array();

		// ダミーCD挿入してパターン取得
		$params['PrefCd'][0] = '99';
		$pattern = Apm_Bukkenurl::getParamPattern($params, Apm_Config_Search::SRHKBN_JYUSYO);

		foreach ($data['selpref'] as $prefData)
		{
			$prefCd = $prefData['M01_TODOUFUKEN_CD'];
			$prefArr[] = $prefCd;

			// 静的URL取得
			$params['PrefName']  = Apm_Config_Search::$TODOUFUKEN_NAME_CODE_LIST[$prefCd];
			$url = Apm_Bukkenurl::generateUrlByParameters($params, $pattern);

			$data['Mapping']["$prefCd"]['PREF_CD']  = $prefCd;
			$data['Mapping']["$prefCd"]['PREF_NM']  = $prefData['M01_TODOUFUKEN'];
			$data['Mapping']["$prefCd"]['PREF_URL'] = $url;
		}

		return $prefArr;
	}

	/**
	* エリア市区町村一覧データを表示用に変換
	*
	* @access	public
	* @param	array ※参照渡し
	* @return	void
	*/
	public function convertAreaCityData(&$data, $params)
	{
		// ダミーCD挿入してパターン取得
		$params['PrefCd'][0] = '99';
		$params['SiteitosiCd'][0] = '99';
		$patternA = Apm_Bukkenurl::getParamPattern($params, Apm_Config_Search::SRHKBN_JYUSYO);
		$params['CityCd'][0] = '999';
		$patternB = Apm_Bukkenurl::getParamPattern($params, Apm_Config_Search::SRHKBN_JYUSYO);

		foreach ($data['selcity'] as $cityData)
		{
			$prefCd = $cityData['TODOUFUKEN_CD'];
			$params['PrefName']  = Apm_Config_Search::$TODOUFUKEN_NAME_CODE_LIST[$prefCd];

			// 指定都市の場合
			if (!empty($cityData['SITEITOSI_ROMAN'])) {
				$params['SiteitosiName'] = $cityData['SITEITOSI_ROMAN'];
				$url  = Apm_Bukkenurl::generateUrlByParameters($params, $patternA);
			}
			// 市区町村の場合
			else if (!empty($cityData['SHIKUCHOUSON_CD'])) {
				$params['CityCd'] = $cityData['SHIKUCHOUSON_CD'];
				$url = Apm_Bukkenurl::generateUrlByParameters($params, $patternB);
			}
			else {
				continue;
			}

			$data['Mapping']["$prefCd"]['CITY_DATA'][] = array(
					 'CITY_NM'		=> $cityData['SHIKUCHOUSON_NAME']
					,'CITY_URL'		=> $url
				);
		}
	}

   /**
	* 町名一覧データを表示用に変換
	*
	* @access	public
	* @param	array ※参照渡し
	* @return	void
	*/
	public function convertCyoData(&$data)
	{
		$data['Mapping'] = array();

		$beforeShikuchosonCd = null;

		foreach ($data['seltown'] as $townData)
		{
			$shikuchosonCd = $townData['SHIKUCHOUSON_CD'];

			// 市区町村が異なる場合
			if (array_key_exists($shikuchosonCd, $data['Mapping']) == false)
			{
				$data['Mapping']["$shikuchosonCd"]['SHIKUCHOUSON_NAME'] = $townData['SHIKUCHOUSON'];
				$data['Mapping']["$shikuchosonCd"]['SHIKUCHOUSON_HEYA_CNT'] = $townData['SHIKUCHOUSON_HEYA_CNT'];
				$data['Mapping']["$shikuchosonCd"][] = array();

			}

			$data['Mapping']["$shikuchosonCd"]['TOWN_DATA'][] = array(
			'CHOUMEI'			=> $townData['CHOUMEI'],
			'CHOUMEI_CD'		=> $townData['CHOUMEI_CD'],
			'REC_CNT'			=> $townData['REC_CNT']
			);
		}
	}

   /**
    * 駅一覧データを表示用に変換
    *
    * @access    public
    * @param    array ※参照渡し
    * @return    void
    */
    public function convertEkinameData(&$data)
    {
        $data['Mapping'] = array();

        foreach ($data['seleki'] as $ekiData)
        {
            // 都道府県CD取得
            $todoufukenCd = $ekiData['TODOUFUKEN_CD'];
            // 都道府県が異なる場合
            if (array_key_exists($todoufukenCd, $data['Mapping']) == false)
            {
                // 都道府県データセット
                $data['Mapping']["$todoufukenCd"]['TODOUFUKEN_CD'] = $ekiData['TODOUFUKEN_CD'];
                $data['Mapping']["$todoufukenCd"]['TODOUFUKEN_NAME'] = $ekiData['TODOUFUKEN'];
                $data['Mapping']["$todoufukenCd"]['EKI_DATA'] = array();

            }

            // 共通駅CD取得
            $renrakuEkiCd = $ekiData['RENRAKU_EKI_CD'];
            // 駅が異なる場合
            if (array_key_exists($renrakuEkiCd, $data['Mapping']["$todoufukenCd"]['EKI_DATA']) == false)
            {
                // 駅データセット
                $data['Mapping']["$todoufukenCd"]['EKI_DATA']["$renrakuEkiCd"]['EKI_CD'] = $ekiData['EKI_CD'];
                $data['Mapping']["$todoufukenCd"]['EKI_DATA']["$renrakuEkiCd"]['EKI_NAME'] = $ekiData['EKI_NAME'];
                $data['Mapping']["$todoufukenCd"]['EKI_DATA']["$renrakuEkiCd"]['REC_CNT'] = $ekiData['REC_CNT'];
                $data['Mapping']["$todoufukenCd"]['EKI_DATA']["$renrakuEkiCd"]['IDO'] = $ekiData['IDO'];
                $data['Mapping']["$todoufukenCd"]['EKI_DATA']["$renrakuEkiCd"]['KEIDO'] = $ekiData['KEIDO'];
                $data['Mapping']["$todoufukenCd"]['EKI_DATA']["$renrakuEkiCd"][] = array();
            }

            // 沿線セット
            $ensenCd = 'ENSEN' . $ekiData['ENSEN_CD'];
            $data['Mapping']["$todoufukenCd"]['EKI_DATA']["$renrakuEkiCd"]['ENSEN_DATA']["$ensenCd"]['ENSEN_NAME'] = $ekiData['ENSEN_NAME'];
        }
    }

    /**
     * パラメータ編集
     *
     * @access    private
     * @param     array
     * @return    string パラメータ
     */
    private function convParam($params)
    {
        // 入力文字の最後に"駅"の字がある場合は、取り除く
        $stWord = $params['StWord'];
        if (!empty($stWord) && $stWord != "駅")
        {
            if (mb_substr($stWord, -1, 1) == "駅")
            {
                $stWord = mb_substr($stWord, 0, mb_strlen($stWord)-1);
            }
        }

        return $stWord;
    }

    /**
     * 入力ワードのログ出力
     *
     * @access    public
     * @param     array
     * @return    void
     */
    public function setKeywordLog($params)
    {

        $db_params = array();
        $sql = $this->generateSetKeywordLogSql($params, $db_params);

        $db = $this->getDbConnection();
        $this->startTransaction($db);
        try {
            $this->dbExecQuery($sql, $db_params, $db);
            $this->commitTransaction($db);
        } catch (Exception $e) {
            $this->rollbackTransaction($db);
            __log_debug(__FILE__, __LINE__, __METHOD__, $e->getMessage());
        }
        $db->closeConnection();
        return;

    }

    /**
     * 入力ワードログ出力用SQL文の取得
     *
     * @access    protected
     * @param     array
     * @param     array ※参照渡し
     * @return    string
     */
    private function generateSetKeywordLogSql($params, &$db_params)
    {

        $db_params['stWord'] = $this->convParam($params);
        $sql  = "";
        $sql .= "INSERT INTO";
        $sql .= "    W32DIRECT_KEYWORD(W32_KEYWORD)";
        $sql .= "VALUES";
        $sql .= "    ( :stWord )";

        return $sql;
    }

    /**
     * 集計テーブルの編集
     *
     * @access    public
     * @param     array
     * @return    void
     */
    public function editSum($params)
    {

        $db_params = array();
        $result = array();
        // 文字列編集
        $db_params['stWord'] = $this->convParam($params);

        $db = $this->getDbConnection();
        $this->startTransaction($db);
        try {
            // DELETE対象取得
            $sql = $this->generateGetDelSumSql();
            $result = $this->dbGetList($sql, $db_params);
            // 条件生成
            $where = $this->createWhereEditSum($result, "T54");
            // DELETE
            if ($where != "")
            {
                $sql = $this->generateDelSumSql($where);
                // 削除
                $this->dbExecQuery($sql, array(), $db);
            }

            // INSERT対象取得
            $sql = $this->generateGetInsSumSql();
            $result = $this->dbGetList($sql, $db_params);
            // 条件生成
            $where = $this->createWhereEditSum($result, "M03");
            // INSERT
            if ($where != "")
            {
                $sql = $this->generateInsSumSql($where);
                // 追加
                $this->dbExecQuery($sql, array(), $db);
            }
            $this->commitTransaction($db);
        } catch (Exception $e) {
            $this->rollbackTransaction($db);
            __log_debug(__FILE__, __LINE__, __METHOD__, $e->getMessage());
        }
        $db->closeConnection();

        return;
    }

    /**
     * 集計テーブルの追加検索用SQL取得
     *
     * @access    private
     * @return    string
     */
    private function generateGetInsSumSql()
    {

        $sql  = "";
        $sql .= "SELECT";
        $sql .= "    M03_TODOUFUKEN_CD AS TODOUFUKEN_CD";
        $sql .= "    ,M03_ENSEN_CD AS ENSEN_CD";
        $sql .= "  FROM";
        $sql .= "    M03EKI M03";
        $sql .= " WHERE";
        $sql .= "    (";
        $sql .= "        M03_EKI_NAME LIKE '%' || :stWord || '%'";
        $sql .= "        OR M03_EKI_NAME LIKE '%' || :stWord || '駅%'";
        $sql .= "    )";
        $sql .= "    AND NOT EXISTS (";
        $sql .= "        SELECT";
        $sql .= "            'X'";
        $sql .= "          FROM";
        $sql .= "            T54EKI_SUM T54";
        $sql .= "         WHERE";
        $sql .= "            M03.M03_TODOUFUKEN_CD = T54.T54_TODOUFUKEN_CD";
        $sql .= "            AND M03.M03_ENSEN_CD = T54.T54_ENSEN_CD";
        $sql .= "    )";
        $sql .= " GROUP BY";
        $sql .= "    M03_TODOUFUKEN_CD";
        $sql .= "    ,M03_ENSEN_CD";

        return $sql;
    }

    /**
     * 集計テーブルの追加用SQL取得
     *
     * @access    private
     * @params    array
     * @return    string
     */
    private function generateInsSumSql($params)
    {

        $sql  = "";
        $sql .= "INSERT INTO T54EKI_SUM";
        $sql .= "(";
        $sql .= "    T54_TODOUFUKEN_CD";
        $sql .= "    ,T54_ENSEN_CD";
        $sql .= "    ,T54_EKI_CD";
        $sql .= "    ,T54_EKI_NAME";
        $sql .= "    ,T54_EKI_CNT";
        $sql .= "    ,T54_KOUSHIN";
        $sql .= "    ,T54_DATA_KUBUN";
        $sql .= ") ";
        $sql .= "SELECT";
        $sql .= "    TODOUFUKEN_CD";
        $sql .= "    ,ENSEN_CD";
        $sql .= "    ,EKI_CD";
        $sql .= "    ,EKI_NAME";
        $sql .= "    ,sum(EKI_HEYA_CNT) AS EKI_CNT";
        $sql .= "    ,sysdate AS KOSHIN";
        $sql .= "    ,0 AS DATA_KBN";
        $sql .= "  FROM";
        $sql .= "    (";
        $sql .= "        SELECT";
        $sql .= "            M03_TODOUFUKEN_CD AS TODOUFUKEN_CD";
        $sql .= "            ,M03_ENSEN_CD AS ENSEN_CD";
        $sql .= "            ,M03_EKI_CD AS EKI_CD";
        $sql .= "            ,M03_EKI_NAME AS EKI_NAME";
        $sql .= "            ,TGT_RENRAKU_CNT AS EKI_HEYA_CNT";
        $sql .= "          FROM";
        $sql .= "            M03EKI";
        $sql .= "            ,(";
        $sql .= "                SELECT";
        $sql .= "                    T63_RENRAKU_CD AS TGT_RENRAKU_CD";
        $sql .= "                    ,SUM(T63_COUNT) AS TGT_RENRAKU_CNT";
        $sql .= "                  FROM";
        $sql .= "                    T63RENRAKU_EKI_SUM ";
        $sql .= "                 WHERE";
        $sql .= "                    T63_DATA_KUBUN = 0";
        $sql .= "                 GROUP BY";
        $sql .= "                    T63_RENRAKU_CD";
        $sql .= "            )";
        $sql .= "         WHERE";
        $sql .= "            M03_RENRAKU_EKI_CD = TGT_RENRAKU_CD";
        $sql .= "            AND M03_RENRAKU_EKI_CD IS NOT NULL";
        $sql .= "            AND (" . $params . ")";
        $sql .= "         GROUP BY";
        $sql .= "            M03_TODOUFUKEN_CD";
        $sql .= "            ,M03_ENSEN_CD";
        $sql .= "            ,M03_EKI_CD";
        $sql .= "            ,M03_EKI_NAME";
        $sql .= "            ,TGT_RENRAKU_CNT";
        $sql .= "        UNION ALL";
        $sql .= "        SELECT";
        $sql .= "            M03_TODOUFUKEN_CD AS TODOUFUKEN_CD";
        $sql .= "            ,M03_ENSEN_CD AS ENSEN_CD";
        $sql .= "            ,M03_EKI_CD AS EKI_CD";
        $sql .= "            ,M03_EKI_NAME AS EKI_NAME";
        $sql .= "            ,COUNT(T03_HEYA_CD) AS EKI_HEYA_CNT";
        $sql .= "          FROM";
        $sql .= "            M03EKI";
        $sql .= "            ,T03WEB_HEYA";
        $sql .= "         WHERE";
        $sql .= "            M03_EKI_CD = T03_EKI_CD01";
        $sql .= "            AND M03_RENRAKU_EKI_CD IS NULL";
        $sql .= "            AND T03_RENRAKU_EKI_CD01 IS NULL";
        $sql .= "            AND (" . $params . ")";
        $sql .= "         GROUP BY";
        $sql .= "            M03_TODOUFUKEN_CD";
        $sql .= "            ,M03_ENSEN_CD";
        $sql .= "            ,M03_EKI_CD";
        $sql .= "            ,M03_EKI_NAME";
        $sql .= "        UNION ALL";
        $sql .= "        SELECT";
        $sql .= "            M03_TODOUFUKEN_CD AS TODOUFUKEN_CD";
        $sql .= "            ,M03_ENSEN_CD AS ENSEN_CD";
        $sql .= "            ,M03_EKI_CD AS EKI_CD";
        $sql .= "            ,M03_EKI_NAME AS EKI_NAME";
        $sql .= "            ,COUNT( T03_HEYA_CD ) AS EKI_HEYA_CNT";
        $sql .= "          FROM";
        $sql .= "            M03EKI";
        $sql .= "            ,T03WEB_HEYA";
        $sql .= "         WHERE";
        $sql .= "            M03_EKI_CD = T03_EKI_CD02";
        $sql .= "            AND M03_RENRAKU_EKI_CD IS NULL";
        $sql .= "            AND T03_RENRAKU_EKI_CD02 IS NULL";
        $sql .= "            AND (" . $params . ")";
        $sql .= "         GROUP BY";
        $sql .= "            M03_TODOUFUKEN_CD";
        $sql .= "            ,M03_ENSEN_CD";
        $sql .= "            ,M03_EKI_CD";
        $sql .= "            ,M03_EKI_NAME";
        $sql .= "        UNION ALL";
        $sql .= "        SELECT";
        $sql .= "            M03_TODOUFUKEN_CD AS TODOUFUKEN_CD";
        $sql .= "            ,M03_ENSEN_CD AS ENSEN_CD";
        $sql .= "            ,M03_EKI_CD AS EKI_CD";
        $sql .= "            ,M03_EKI_NAME AS EKI_NAME";
        $sql .= "            ,COUNT( T03_HEYA_CD ) AS EKI_HEYA_CNT";
        $sql .= "          FROM";
        $sql .= "            M03EKI";
        $sql .= "            ,T03WEB_HEYA";
        $sql .= "         WHERE";
        $sql .= "            M03_EKI_CD = T03_EKI_CD03";
        $sql .= "            AND M03_RENRAKU_EKI_CD IS NULL";
        $sql .= "            AND T03_RENRAKU_EKI_CD03 IS NULL";
        $sql .= "            AND (" . $params . ")";
        $sql .= "         GROUP BY";
        $sql .= "            M03_TODOUFUKEN_CD";
        $sql .= "            ,M03_ENSEN_CD";
        $sql .= "            ,M03_EKI_CD";
        $sql .= "            ,M03_EKI_NAME";
        $sql .= "    )";
        $sql .= " GROUP BY";
        $sql .= "    TODOUFUKEN_CD";
        $sql .= "    ,ENSEN_CD";
        $sql .= "    ,EKI_CD";
        $sql .= "    ,EKI_NAME";

        return $sql;
    }

    /**
     * 集計テーブルの削除検索用SQL取得
     *
     * @access    private
     * @return    string
     */
    private function generateGetDelSumSql()
    {

        $sql  = "";
        $sql .= "SELECT";
        $sql .= "    M03_TODOUFUKEN_CD AS TODOUFUKEN_CD";
        $sql .= "    ,M03_ENSEN_CD AS ENSEN_CD";
        $sql .= "  FROM";
        $sql .= "    M03EKI M03";
        $sql .= " WHERE";
        $sql .= "    (";
        $sql .= "        M03_EKI_NAME LIKE '%' || :stWord || '%'";
        $sql .= "        OR M03_EKI_NAME LIKE '%' || :stWord || '駅%'";
        $sql .= "    )";
        $sql .= "    AND EXISTS (";
        $sql .= "        SELECT";
        $sql .= "            'X'";
        $sql .= "          FROM";
        $sql .= "            T54EKI_SUM T54";
        $sql .= "         WHERE";
        $sql .= "            M03.M03_TODOUFUKEN_CD = T54.T54_TODOUFUKEN_CD";
        $sql .= "            AND M03.M03_ENSEN_CD = T54.T54_ENSEN_CD";
        $sql .= "            AND T54.T54_DATA_KUBUN = 0";
        $sql .= "            AND T54.T54_KOUSHIN < SYSDATE - 30 / 1440";
        $sql .= "    )";
        $sql .= " GROUP BY";
        $sql .= "    M03_TODOUFUKEN_CD";
        $sql .= "    ,M03_ENSEN_CD";

        return $sql;
    }

    /**
     * 集計テーブルの削除用SQL取得
     *
     * @access    private
     * @params    array
     * @return    string
     */
    private function generateDelSumSql($params)
    {

        $sql  = "";
        $sql .= "DELETE FROM";
        $sql .= "    T54EKI_SUM";
        $sql .= " WHERE";
        $sql .= "    (" . $params . ")";
        $sql .= "    AND T54_DATA_KUBUN = 0";

        return $sql;
    }

    /**
     * 集計テーブル編集時のWHERE句生成
     *
     * @access    private
     * @params    array
     * @return    string
     */
    private function createWhereEditSum($params, $tblKbn)
    {

        $sql = "";
        foreach ($params as $ensen)
        {
            $sql .= " OR (" . $tblKbn . "_TODOUFUKEN_CD = '" . $ensen['TODOUFUKEN_CD'] . "' AND " . $tblKbn . "_ENSEN_CD = '" . $ensen['ENSEN_CD'] . "')";
        }
        if ($sql != "")
        {
            $sql = substr($sql, 3, strlen($sql));
        }

        return $sql;
    }

	/**
	 * 店舗検索（左パネル）のキーワードから、店舗を取得
	 * @param unknown_type $params
	 */
    public function getSearchShopList($params)
    {
        $db_params = array();
        $sql = $this->generateShopListSql($params, $db_params, true);
        return $this->dbGetList($sql, $db_params);
    }

    /**
     * 店舗電話番号一覧データを表示用に変換
     * @param unknown_type $telList
     * @return 表示用データリスト
     */
    public function convertShopListData($telList, $preflist)
    {
    	$DispList = array();
    	$indexNo = 0;
        $beforePrefCd = null;
    	$beforeCityCd = null;

    	foreach (Apm_Config_Shop::$PREF_LIST as $key => $value)
    	{
    		$DispList["$key"]['PrefName'] = $value;
    		$DispList["$key"]['CityList'] = array();
    	}

        foreach ($telList as $telData)
        {
            $PrefCd = $telData['M05_TENPO_TODOUFUKEN_CD'];
            $CityCd = $telData['M05_TENPO_SHIKUCHOUSON_CD'];

            // 都道府県が異なる場合
            if ($beforePrefCd != $PrefCd)
            {
                $beforeCityCd = null;
            }

            // 市区町村が異なる場合
            if ($beforeCityCd != $CityCd)
            {
                $DispList["$PrefCd"]['CityList'][] = array(
                    'CityCd'    => $CityCd,
                    'CityName'  => $telData['M05_TENPO_SHIKUCHOUSON'],
                    'TenpoList' => array()
                );
                $indexNo = count($DispList["$PrefCd"]['CityList']) - 1;
            }

            $DispList["$PrefCd"]['CityList'][$indexNo]['TenpoList'][] = array(
                'M05_HOUZIN_CD'          => $telData['M05_HOUZIN_CD'],
                'M05_TENPO_CD'           => $telData['M05_TENPO_CD'],
                'M05_TENPO_NAME'         => $telData['M05_TENPO_NAME'],
                'INFOMATION_CENTER_FLAG' => $telData['INFOMATION_CENTER_FLAG'],
                'M05_OEM'                => $telData['M05_OEM'],
                'M93_CONNECTION_NO6'     => $telData['M93_CONNECTION_NO6']
            );

            $beforePrefCd = $PrefCd;
            $beforeCityCd = $CityCd;
        }

        // 不要な都道府県リストは削除
        foreach($DispList as $key => $val){
            if(empty($val['CityList'])){
            	unset($DispList[$key]);
            }
        }

        return $DispList;
    }

    /**
     * 店舗電話番号一覧データ検索用SQL文の取得
     *
     * 店舗電話番号一覧データ検索用SQL文の取得を行う
     *
     * @param unknown_type $params
     * @param unknown_type $db_params
     * @param unknown_type $condFlg
     */
    protected function generateShopListSql($params, &$db_params)
    {
		/** 現在未使用 - 使用する場合はWebCalling注意のこと **/

        foreach($params as $key => $val){
        	$db_params['prefCd'.$key] = $val['M01_TODOUFUKEN_CD'];
        }

        $sql  = "";
        $sql .= "SELECT";
        $sql .= "    M05I.M05_HOUZIN_CD";
        $sql .= "    ,M05I.M05_TENPO_CD";
        $sql .= "    ,M05I.M05_TENPO_BANGOU";
        $sql .= "    ,M05I.M05_TENPO_TODOUFUKEN_CD";
        $sql .= "    ,M05I.M05_TENPO_TODOUFUKEN";
        $sql .= "    ,M05I.M05_TENPO_SHIKUCHOUSON_CD";
        $sql .= "    ,M05I.M05_TENPO_SHIKUCHOUSON";
        $sql .= "    ,M05I.M05_TENPO_NAME";
        $sql .= "    ,M05I.M05_OEM";
        $sql .= "    ,SUBSTR(M05S.M05_SERVICE_FLAG, 12, 1) AS INFOMATION_CENTER_FLAG";
        $sql .= "    ,M93.M93_CONNECTION_NO6";
        $sql .= "  FROM";
        $sql .= "     M05TENPO_INFO M05I";
        $sql .= "    ,M05TENPO_SERVICE M05S";
        $sql .= "    ,M93WEBCALLING M93";
        $sql .= " WHERE";
        $sql .= "    M05I.M05_KOUKAI_FLAG = 1";
        $sql .= "    AND (";
        $sql .= "        M05I.M05_TENPO_CD > '01000000'";
        $sql .= "        AND M05I.M05_TENPO_CD < '48000000'";
        $sql .= "    )";
        $sql .= "    AND M05I.M05_TENPO_CD = M05S.M05_TENPO_CD";
        $sql .= "    AND M05I.M05_TENPO_CD = M93.M93_TENPO_CD";
        $sql .= "    AND M05I.M05_TENPO_TODOUFUKEN_CD IN ( ";

        $sepa = "";
        foreach($db_params as $key => $val){
        	$sql .= $sepa . " :".$key;
        	$sepa = ",";
        }
        $sql .= " )";
        $sql .= " ORDER BY";
        $sql .= "     M05I.M05_TENPO_TODOUFUKEN_CD ASC";
        $sql .= "    ,M05I.M05_TENPO_SHIKUCHOUSON_CD ASC";
        $sql .= "    ,M05I.M05_TENPO_CD ASC";

    	return $sql;
    }

    /**
     * 都道府県データリストの取得
     *
     * @return array $return 都道府県リスト
     */
    public function generateTodohukenListSql()
    {

		$sql  = "";
		$sql .= "SELECT ";
		$sql .= "     distinct M43.M43_TODOUFUKEN_CD as TODOUFUKEN_CD ";
		$sql .= "    ,M43.M43_TODOUFUKEN as TODOUFUKEN ";
		$sql .= "FROM ";
		$sql .= "     M43TODOUFUKEN M43 ";
		$sql .= "ORDER BY ";
		$sql .= "     M43.M43_TODOUFUKEN_CD ";

        return $sql;
    }

    /*
     * 物件一覧都道府県のエリア
     */
    public function getShikuchousonOfPref($prefCd){
        $sql = " SELECT M43_SHIKUCHOUSON_CD AS SHIKUCHOUSON_CD, M43_SHIKUCHOUSON AS SHIKUCHOUSON
                FROM M43SHIKUCHOUSON WHERE M43_TODOUFUKEN_CD = :prefCd
                ORDER BY M43_SHIKUCHOUSON_CD
	        ";
        $db_params = array();
        $db_params['prefCd'] = $prefCd;
        return $this->dbGetList($sql, $db_params);
    }

    /*
     * Ajax駅検索
     * $ekiName:駅名
     * $equal:完全検索か、前方一致か（true:完全,false:前方一致)
     */
    function getAjaxEki($ekiName,$equal){

        $db_params = array();

        $stWord = $ekiName;
        $db_params['stWord'] = $stWord;

        $select = "SELECT DISTINCT PR.M03_TODOUFUKEN_CD AS TODOUFUKEN_CD,
        				PR.M03_TODOUFUKEN AS TODOUFUKEN,
        				PR.M03_EKI_NAME AS EKI_NAME,
        				(
        					SELECT CAST(WM_CONCAT(SUB.M03_EKI_CD) AS VARCHAR(1024)) FROM M03EKI SUB
        					WHERE SUB.M03_TODOUFUKEN_CD = PR.M03_TODOUFUKEN_CD AND SUB.M03_EKI_NAME = PR.M03_EKI_NAME
        				) AS FULL_EKI_CD";

        if(!$equal){//完全一致
            $sql = $select . " FROM M03EKI PR where PR.M03_EKI_NAME = :stWord OR PR.M03_EKI_ALIAS = :stWord ORDER BY PR.M03_TODOUFUKEN_CD ";
        }
        else {
            $sql = $select . " FROM M03EKI PR WHERE PR.M03_EKI_NAME LIKE :stWord || '%' OR PR.M03_EKI_ALIAS LIKE :stWord || '%' ORDER BY PR.M03_TODOUFUKEN_CD ";
        }
        return $this->dbGetList($sql, $db_params);


    }

    /*
     * Ajax駅検索 (ROSENZU_EKI_CD)取得用
    * $ekiName:駅名
    * $equal:完全検索か、前方一致か（true:完全,false:前方一致)
    */
    function getAjaxEkiRosenCode($ekiName,$equal){

    	$db_params = array();

    	$stWord = $ekiName;
    	$db_params['stWord'] = $stWord;

    	$select = "SELECT DISTINCT PR.M03_TODOUFUKEN_CD AS TODOUFUKEN_CD,
        				PR.M03_TODOUFUKEN AS TODOUFUKEN,
        				PR.M03_EKI_NAME AS EKI_NAME,
        				(
        					SELECT CAST(WM_CONCAT(DISTINCT SUB.M03_ROSENZU_EKI_CD) AS VARCHAR(1024)) FROM M03EKI SUB
        					WHERE SUB.M03_TODOUFUKEN_CD = PR.M03_TODOUFUKEN_CD AND SUB.M03_EKI_NAME = PR.M03_EKI_NAME
        				) AS FULL_ROSENZU_EKI_CD
    					,(SELECT CAST(WM_CONCAT(SUB.M03_EKI_CD) AS VARCHAR(1024)) FROM M03EKI SUB
        					WHERE SUB.M03_TODOUFUKEN_CD = PR.M03_TODOUFUKEN_CD AND SUB.M03_EKI_NAME = PR.M03_EKI_NAME
        				) AS FULL_EKI_CD";

    	if(!$equal){//完全一致
    		$sql = $select . " FROM M03EKI PR where PR.M03_EKI_NAME = :stWord OR PR.M03_EKI_ALIAS = :stWord ORDER BY PR.M03_TODOUFUKEN_CD ";
    	}
    	else {
    		$sql = $select . " FROM M03EKI PR WHERE PR.M03_EKI_NAME LIKE :stWord || '%' OR PR.M03_EKI_ALIAS LIKE :stWord || '%' ORDER BY PR.M03_TODOUFUKEN_CD ";
    	}
    	return $this->dbGetList($sql, $db_params);


    }
    // 20141215 S_Add
    /**
     *店舗検索（左パネル）のキーワードから、店舗を取得2
     * @param unknown_type $params
    */
    public function getSearchShopList2($params)
    {
    	$db_params = "";

        $sql  = "";
        $sql .= "SELECT";
        $sql .= "    M05I.M05_HOUZIN_CD";
        $sql .= "    ,M05I.M05_TENPO_CD";
        $sql .= "    ,M05I.M05_TENPO_BANGOU";
        $sql .= "    ,M05I.M05_TENPO_TODOUFUKEN_CD";
        $sql .= "    ,M05I.M05_TENPO_TODOUFUKEN";
        $sql .= "    ,M05I.M05_TENPO_SHIKUCHOUSON_CD";
        $sql .= "    ,M05I.M05_TENPO_SHIKUCHOUSON";
        $sql .= "    ,M05I.M05_TENPO_NAME";
        $sql .= "    ,M05I.M05_OEM";
        $sql .= "    ,M05I.M05_EKI_CD01";
        $sql .= "    ,SUBSTR(M05S.M05_SERVICE_FLAG, 12, 1) AS INFOMATION_CENTER_FLAG";
        $sql .= "    ,M93.M93_CONNECTION_NO6";
        $sql .= "  FROM";
        $sql .= "     M05TENPO_INFO M05I";
        $sql .= "    ,M05TENPO_SERVICE M05S";
        $sql .= "    ,M93WEBCALLING M93";
        $sql .= " WHERE";
        $sql .= "    M05I.M05_KOUKAI_FLAG = 1";
        $sql .= "    AND (";
        $sql .= "        M05I.M05_TENPO_CD > '01000000'";
        $sql .= "        AND M05I.M05_TENPO_CD < '48000000'";
        $sql .= "    )";
        $sql .= "    AND M05I.M05_TENPO_CD = M05S.M05_TENPO_CD";
        $sql .= "    AND M05I.M05_TENPO_CD = M93.M93_TENPO_CD";

        if ($params ['todoufukenCd']) {
            $sql .= "    AND M05I.M05_TENPO_TODOUFUKEN_CD = ";
            $sql .= "'" . $params ['todoufukenCd'] . "'";
        }
        if ($params ['shikuchosonCd']) {
            $sql .= "    AND TRIM(M05I.M05_TENPO_TODOUFUKEN_CD || M05I.M05_TENPO_SHIKUCHOUSON_CD) = ";
            $sql .= "'" . $params ['shikuchosonCd'] . "'";
        }
        if ($params ['ekiCd']) {
        	$sql .= "    AND M05I.M05_EKI_CD01 = ";
        	$sql .= "'" . $params ['ekiCd'] . "'";
        }
        //tho add
        if ($params['ekiCdList'] && count($params['ekiCdList']) > 0){
        	$sql .= "    AND M05I.M05_EKI_CD01 IN ";
        	$sql .= "('" . join("','", $params ['ekiCdList']) . "')";
        }

        $sql .= " ORDER BY";
        $sql .= "     M05I.M05_TENPO_TODOUFUKEN_CD ASC";
        $sql .= "    ,M05I.M05_TENPO_SHIKUCHOUSON_CD ASC";
        $sql .= "    ,M05I.M05_TENPO_CD ASC";

        return $this->dbGetList($sql, $db_params);
    }
    // 20141215 E_Add
}
?>